/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataLayer;

import Objects.Game;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Philipp
 */
public class dbDal extends AbstractDb implements DataLayerInterface {

    private static dbDal instance;

    private dbDal() {
    }

    public static dbDal getInstance() {
        if (dbDal.instance == null) {
            dbDal.instance = new dbDal();
        }
        return dbDal.instance;
    }

    @Override
    public ArrayList<Game> getGameList(String username, String pwd) {
        if (this.login(username, pwd)) {
            System.out.println("dbDal@getGameList: Login OK, erstelle GameList für: " + username);
        } else {
            System.out.println("dbDal@getGameList: FALSCH, bye");
            return new ArrayList<Game>();
        }

        ArrayList<Game> list = new ArrayList<Game>();
        try {
            java.sql.PreparedStatement pStatement = getDatabase().prepare("SELECT contenttab.title, contenttab.contentid FROM contenttab WHERE 1");
            java.sql.ResultSet queryResult = getDatabase().execute(pStatement);
            while (queryResult.next()) {
                //System.out.println("output@2:" + queryResult.getString(2)); //id
                list.add(new Game(queryResult.getString(1), queryResult.getInt(2)));
            }

        } catch (SQLException ex) {
            System.err.println("dbDal@getGameList: SQLException: " + ex.getMessage());
        }

        return list;
    }

    @Override
    public ArrayList<Game> getMyGames(String username) {
        ArrayList<Game> list = new ArrayList<Game>();
        try {
            java.sql.PreparedStatement pStatement = getDatabase().prepare("SELECT contenttab.title, contenttab.contentid FROM contenttab, license WHERE license.username_fk= ? AND license.contentid_fk=contenttab.contentid");
            pStatement.setString(1, username);
            java.sql.ResultSet queryResult = getDatabase().execute(pStatement);
            while (queryResult.next()) {
                //System.out.println("output@2:" + queryResult.getString(2)); //id
                list.add(new Game(queryResult.getString(1), queryResult.getInt(2)));
            }
        } catch (SQLException ex) {
            System.err.println("dbDal@getGameList: SQLException: " + ex.getMessage());
        }
        return list;
    }

    @Override
    public Blob getLicense(String username, int gameId) {
        try {

//            int keys = java.sql.Statement.RETURN_GENERATED_KEYS;
            java.sql.PreparedStatement pStatement = getDatabase().prepare("SELECT encsymkey FROM license WHERE username_fk = ? AND contentid_fk = ?");

            pStatement.setString(1, username);
            pStatement.setInt(2, gameId);
            java.sql.ResultSet queryResult = getDatabase().execute(pStatement);

            if (queryResult.next()) {
                return queryResult.getBlob(1);
            }
        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        return null;
    }

    private boolean login(String user, String pwd) {
        try {
            java.sql.PreparedStatement pStatement = getDatabase().prepare("SELECT * FROM usertab WHERE username = ? AND password = ?");
            pStatement.setString(1, user);
            pStatement.setString(2, pwd);
            java.sql.ResultSet queryResult = getDatabase().execute(pStatement);
            if (queryResult.next()) {
                return true;
            }
        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        return false;
    }

//    public int insertNewKey(byte[] keyFile) {
//
//        try {
////            java.sql.PreparedStatement pStatement = getDatabase().prepare("INSERT INTO symenckeytab (encryptionkey) VALUES(?)");
//            java.sql.PreparedStatement pStatement = getDatabase().prepare("INSERT INTO `license` (`keyid`, `encryptionkey`) VALUES ( NULL , ?);");
//            InputStream blobData = new ByteArrayInputStream(keyFile);
//            pStatement.setBinaryStream(1, blobData);
//
//            int queryResult = getDatabase().executeUpdate(pStatement);
//
////            Insert done, per select ID abfragen
//
////            java.sql.PreparedStatement pStatement2 = getDatabase().prepare("SELECT keyid FROM `symenckeytab` WHERE `keyid` = ? ;");
//            //pStatement2.setBinaryStream(1, blobData);
////            pStatement2.setInt(1, 20);
////            java.sql.ResultSet queryResult = getDatabase().execute(pStatement2);
//            if (queryResult == 1) {
//                System.out.println("dbDal@insertNewKey: Insert gutgegangen");
//                //System.out.println("dbDal@insertNewKey: " + queryResult.getInt(1));
//                //return queryResult.getInt(1);
//                return 1;
//            }
//
//        } catch (SQLException ex) {
//            System.err.println("dbDal@insertNewKey: SQLException: " + ex.getMessage());
//        }
//        return -1;
//    }
    @Override
    public Blob getGame(int gameId) {
        try {
            java.sql.PreparedStatement pStatement = getDatabase().prepare("SELECT contentbinary FROM contenttab WHERE contentid = ?");
            pStatement.setInt(1, gameId);
            java.sql.ResultSet queryResult = getDatabase().execute(pStatement);
            if (queryResult.next()) {
                return queryResult.getBlob(1);
            }
        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        return null;
    }

    @Override
    public Blob getSymKey(String user, int gameId) {
        try {
            java.sql.PreparedStatement pStatement = getDatabase().prepare("SELECT symkey FROM license WHERE username_fk = ? AND contentid_fk = ?");
            pStatement.setString(1, user);
            pStatement.setInt(2, gameId);

            java.sql.ResultSet queryResult = getDatabase().execute(pStatement);
            if (queryResult.next()) {
                return queryResult.getBlob(1);
            }
        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        return null;
    }

    @Override
    public boolean insertLicense(int contentid_fk, String username_fk, byte[] encsymkey, byte[] symkey) {
        try {
            java.sql.PreparedStatement pStatement = getDatabase().prepare("INSERT INTO license (`contentid_fk` ,`username_fk` ,`encsymkey` ,`symkey`) VALUES ( ? , ? , ? , ? )");
            pStatement.setInt(1, contentid_fk);
            pStatement.setString(2, username_fk);

            InputStream blobData = new ByteArrayInputStream(encsymkey);
            pStatement.setBinaryStream(3, blobData);

            InputStream blobData2 = new ByteArrayInputStream(symkey);
            pStatement.setBinaryStream(4, blobData2);

            int queryResult = getDatabase().executeUpdate(pStatement);
            if (queryResult == 1) {
                return true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(dbDal.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    @Override
    public Blob getCertByUserName(String username) {
        try {
            java.sql.PreparedStatement pStatement = getDatabase().prepare("SELECT zertifikat FROM usertab WHERE username = ?");
            pStatement.setString(1, username);
            java.sql.ResultSet queryResult = getDatabase().execute(pStatement);

            if (queryResult.next()) {
                return queryResult.getBlob(1);
            }
        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        return null;

    }
}
